from rest_framework import serializers
from rest_framework.viewsets import ModelViewSet
from labor_price.models import Template
import json
from rest_framework.decorators import action
from rest_framework.response import Response
import numpy as np
import pandas as pd
from sqlalchemy import create_engine


class TemplateSerializer(serializers.ModelSerializer):
    # projectList_id = serializers.IntegerField()
    # projectList = serializers.SlugRelatedField(slug_field="name", read_only=True)

    class Meta:
        model = Template
        fields = '__all__'


class TemplateView(ModelViewSet):
    queryset = Template.objects.all()  # 查询集
    serializer_class = TemplateSerializer  # 序列化器

    @action(methods=['get'], detail=False)
    def batch_del(self, request):
        del_list = request.GET.get('del_list')
        del_list = json.loads(del_list)
        print(del_list)
        #  修改status状态  和关联该数据的状态    获取数据也需要根据status获取
        try:
            for i in (del_list):
                Template.objects.get(id=i).delete()
        except:
            return Response({'msg': "批量删除失败", 'code': 400, 'data': '存在关联数据；不允许删除'})
        return Response({'msg': "批量删除成功", 'code': 200})

    @action(methods=['post'], detail=False)
    def stu_excel(self, request):
        file_obj = request.FILES.get("file")
        banji = request.POST.get('banji')

        # excel_raw_data = pd.read_excel(file_obj, dtype={'身份证号': np.str})
        excel_raw_data = pd.read_excel(file_obj)
        excel_raw_data.columns = ['name', 'sex', 'age', 'phone', 'nativeplace', 'linkman', 'linkman_relation',
                                  'linkman_phone']
        # excel_raw_data = excel_raw_data.dropna(subset=['name', 'id_card', 'car_number', 'car_type', 'ismotor'])   #  删除缺失行
        excel_raw_data = excel_raw_data.dropna()  # 删除缺失行
        excel_raw_data['sex'] = excel_raw_data['sex'].replace('男', True).replace(
            '女', False)
        excel_raw_data['stu_class_id'] = banji
        # excel_raw_data.drop_duplicates('id_card', inplace=True)  #  删除重复行
        phone_list = excel_raw_data['phone'].to_list()
        linkman_phone_list = excel_raw_data['linkman_phone'].to_list()

        for i in phone_list:
            li = StudentsInformation.objects.filter(phone=i)
            if li:
                excel_raw_data.drop(index=excel_raw_data[excel_raw_data['phone'] == i].index[0], inplace=True)
                continue
        for i in linkman_phone_list:
            li = StudentsInformation.objects.filter(linkman_phone=i)
            if li:
                excel_raw_data.drop(indelocalhostx=excel_raw_data[excel_raw_data['linkman_phone'] == i].index[0], inplace=True)
                continue

        # excel_raw_data = excel_raw_data.drop('id_card', axis=1)
        print(excel_raw_data, 156156)
        try:
            engine = create_engine('mysql+pymysql://root:123456@101.34.188.212:3306/interview')
            excel_raw_data.to_sql('firstapp_studentsinformation', engine, if_exists="append", index=False,
                                  chunksize=200)
        except:
            return Response({'msg': '上传失败请检查表内容', 'status': 'No'})

        # conn = pymysql.connect(host="rm-2zet350743z1p9j48co.mysql.rds.aliyuncs.com", port=3306, user="test121",
        #                        password="Test123456", database="oasystem2", charset="utf8")
        # sql = "insert into 'information_Vehicle' (name, sex, age, phone, nativeplace, linkman, linkman_relation, linkman_phone, stu_class) VALUES (%(name)s, %(sex)s, %(age)s, %(phone)s, %(nativeplace)s, %(linkman)s, %(linkman_relation)s, %(linkman_phone)s, %(stu_class)s)"
        # df = pd.read_sql(sql, conn)
        # print(df)
        return Response({'msg': '批量上传成功', 'status': 'Yes'})
